{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:40.247228Z",
     "iopub.status.busy": "2023-11-13T13:27:40.247041Z",
     "iopub.status.idle": "2023-11-13T13:27:40.715466Z",
     "shell.execute_reply": "2023-11-13T13:27:40.714796Z",
     "shell.execute_reply.started": "2023-11-13T13:27:40.247204Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "stage = 'B'\n",
    "root = '../../../contest'\n",
    "\n",
    "df_train = pd.read_csv(os.path.join(root, 'train/GSLD_ASSET_DEBT.csv'))\n",
    "df_test = pd.read_csv(os.path.join(root, '{}/GSLD_ASSET_DEBT_{}.csv'.format(stage, stage)))\n",
    "\n",
    "save_path = '../data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:40.716860Z",
     "iopub.status.busy": "2023-11-13T13:27:40.716655Z",
     "iopub.status.idle": "2023-11-13T13:27:40.731854Z",
     "shell.execute_reply": "2023-11-13T13:27:40.731218Z",
     "shell.execute_reply.started": "2023-11-13T13:27:40.716835Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "def prepro(df):\n",
    "    df = df.drop('DATA_DAT',axis=1)\n",
    "    for col in df.columns[1:]:\n",
    "        df[col] = df[col].fillna(0).apply(lambda x: round(pow(x/3.12,3),2))\n",
    "    \n",
    "    df = df.rename(columns={\n",
    "        'AST_DP_BAL':'AST_DAY_DP_BAL', \n",
    "        'DEBT_LOAN_BAL':'DEBT_DAY_LOAN_BAL',\n",
    "        'DEBT_LOAN_BAL_MAVER':'DEBT_MAVER_LOAN_BAL',\n",
    "        'DEBT_LOAN_BAL_SAVER':'DEBT_SAVER_LOAN_BAL',\n",
    "        'DEBT_LOAN_BAL_YAVER':'DEBT_YAVER_LOAN_BAL',\n",
    "    })\n",
    "\n",
    "    period_list = ['day', 'maver', 'saver', 'yaver']\n",
    "\n",
    "    for i, period in enumerate(period_list):\n",
    "        PERIOD = period.upper()\n",
    "        df['ast_'+period+'_aum_bal'] = df['AST_'+PERIOD+'_FA_BAL'] - (df['DEBT_'+PERIOD+'_LOAN_BAL'] / 2) # 计算aum 金融资产-1/2贷款\n",
    "        df['ast_'+period+'_otr_bal'] = df['ast_'+period+'_aum_bal'] - df['AST_'+PERIOD+'_DP_BAL'] # 计算其他资产 aum-存款\n",
    "\n",
    "    # 计算aum、其他资产和贷款的日月季年差值\n",
    "    for i, pi in enumerate(period_list):\n",
    "        for j in range(i+1, len(period_list)):\n",
    "            pj = period_list[j]\n",
    "            PI, PJ = pi.upper(), pj.upper()\n",
    "            df['ast_'+pi+'_diff_'+pj+'_aum'] = df['ast_'+pj+'_aum_bal'] - df['ast_'+pi+'_aum_bal'] # aum日月季年的差值\n",
    "            df['ast_'+pi+'_diff_'+pj+'_otr'] = df['ast_'+pj+'_otr_bal'] - df['ast_'+pi+'_otr_bal'] # 其他资产日月季年的差值\n",
    "            df['ast_'+pi+'_diff_'+pj+'_loan'] = df['DEBT_'+PJ+'_LOAN_BAL'] - df['DEBT_'+PI+'_LOAN_BAL']  # 贷款日月季年的差值\n",
    "\n",
    "    # 计算金融资产、存款日月季年差值\n",
    "    for fa in ['fa', 'dp']:\n",
    "        for i, pi in enumerate(period_list):\n",
    "            FA, PI = fa.upper(), pi.upper()\n",
    "            df['ast_'+pi+'_diff_max_'+fa] = df['AST_'+FA+'_BAL_MAX'] - df['AST_'+PI+'_'+FA+'_BAL'] # 与AST_FA_BAL_MAX的差值\n",
    "            for j in range(i+1, len(period_list)):\n",
    "                pj, PJ = period_list[j], period_list[j].upper()\n",
    "                df['ast_'+pi+'_diff_'+pj+'_'+fa] = df['AST_'+PJ+'_'+FA+'_BAL'] - df['AST_'+PI+'_'+FA+'_BAL'] # 日月季年之间的差值\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:40.733010Z",
     "iopub.status.busy": "2023-11-13T13:27:40.732838Z",
     "iopub.status.idle": "2023-11-13T13:27:41.865330Z",
     "shell.execute_reply": "2023-11-13T13:27:41.864741Z",
     "shell.execute_reply.started": "2023-11-13T13:27:40.732988Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = prepro(df_train)\n",
    "df_test = prepro(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:27:41.866517Z",
     "iopub.status.busy": "2023-11-13T13:27:41.866315Z",
     "iopub.status.idle": "2023-11-13T13:27:47.404300Z",
     "shell.execute_reply": "2023-11-13T13:27:47.403704Z",
     "shell.execute_reply.started": "2023-11-13T13:27:41.866492Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train.to_csv(save_path+'/GSLD_ASSET_DEBT.csv', index=False)\n",
    "df_test.to_csv(save_path+'/GSLD_ASSET_DEBT_{}.csv'.format(stage), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
